from flask import Flask, render_template, redirect, url_for, request, flash, send_file, session, jsonify
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime, timedelta
from werkzeug.security import generate_password_hash, check_password_hash
import os
import json
import io
from docx import Document
from docx.shared import Pt, Cm, Inches
from docx.enum.text import WD_ALIGN_PARAGRAPH
from sqlalchemy.orm import joinedload, contains_eager, subqueryload
from sqlalchemy import func, and_, or_, case

# 导入原始应用中的模型
from app import db, User, Project, ProjectTask, ProjectMilestone, ProjectCost, WeeklyReport, TaskDependency, ProjectRole

# 查询优化辅助函数
def get_user_projects_optimized(user_id=None):
    """优化获取用户项目列表的查询"""
    if user_id is None:
        from flask import session
        user_id = session.get('user_id')  # 使用session获取用户ID，避免AnonymousUserMixin错误
    
    if user_id is None:
        return []
    
    # 获取用户角色
    user = User.query.get(user_id)
    if not user:
        return []
    
    if user.role == 'admin':
        # 管理员可以看到所有项目，使用joinedload预加载关联数据
        projects = Project.query.options(
            joinedload(Project.manager),
            joinedload(Project.customer),
            joinedload(Project.tasks).joinedload(ProjectTask.assignee),
            joinedload(Project.milestones)
        ).all()
    else:
        # 普通用户只能看到参与的项目，使用子查询优化
        user_project_ids = db.session.query(ProjectRole.project_id).filter_by(user_id=user_id).subquery()
        projects = Project.query.options(
            joinedload(Project.manager),
            joinedload(Project.customer),
            joinedload(Project.tasks).joinedload(ProjectTask.assignee),
            joinedload(Project.milestones)
        ).filter(Project.id.in_(user_project_ids)).all()
    
    return projects

def get_project_details_optimized(project_id):
    """优化获取项目详情的查询"""
    if project_id is None:
        return None
    
    # 使用joinedload一次性加载所有关联数据，避免复杂的嵌套加载
    project = Project.query.options(
        joinedload(Project.manager),
        joinedload(Project.customer),
        joinedload(Project.tasks).joinedload(ProjectTask.assignee),
        joinedload(Project.milestones),
        joinedload(Project.costs),
        joinedload(Project.roles).joinedload(ProjectRole.user)
    ).filter_by(id=project_id).first()
    
    return project

def calculate_project_health_optimized(project):
    """优化计算项目健康度指标的函数"""
    if project is None:
        return None
    
    today = datetime.now().date()
    
    # 计算延期天数
    if project.end_date:
        end_date = project.end_date.date() if isinstance(project.end_date, datetime) else project.end_date
        if end_date < today:
            project.delay_days = (today - end_date).days
        else:
            project.delay_days = 0
    else:
        project.delay_days = 0
    
    # 优化查询：一次性获取所有任务统计信息
    task_stats = db.session.query(
        func.count(ProjectTask.id).label('total_tasks'),
        func.sum(case([(ProjectTask.status == '已完成', 1)], else_=0)).label('completed_tasks')
    ).filter_by(project_id=project.id).first()
    
    total_tasks = task_stats.total_tasks or 0
    completed_tasks = task_stats.completed_tasks or 0
    project.task_completion_rate = (completed_tasks / total_tasks * 100) if total_tasks > 0 else 0
    
    # 优化查询：一次性获取所有里程碑统计信息
    milestone_stats = db.session.query(
        func.count(ProjectMilestone.id).label('total_milestones'),
        func.sum(case([(ProjectMilestone.status == '已完成', 1)], else_=0)).label('completed_milestones')
    ).filter_by(project_id=project.id).first()
    
    total_milestones = milestone_stats.total_milestones or 0
    completed_milestones = milestone_stats.completed_milestones or 0
    project.milestone_completion_rate = (completed_milestones / total_milestones * 100) if total_milestones > 0 else 0
    
    # 优化查询：一次性获取项目总成本
    total_budget = project.budget
    actual_cost = db.session.query(func.sum(ProjectCost.amount)).filter_by(project_id=project.id).scalar() or 0
    project.cost_usage_rate = (actual_cost / total_budget * 100) if total_budget > 0 else 0
    
    # 计算项目健康度评分（0-100）
    health_score = 0
    
    # 进度健康度（40%权重）
    if project.delay_days <= 0:
        health_score += 40
    elif project.delay_days <= 3:
        health_score += 25
    elif project.delay_days <= 7:
        health_score += 10
    
    # 任务完成率（30%权重）
    health_score += project.task_completion_rate * 0.3
    
    # 里程碑完成率（20%权重）
    health_score += project.milestone_completion_rate * 0.2
    
    # 成本控制（10%权重）
    if project.cost_usage_rate <= 100:
        health_score += 10
    elif project.cost_usage_rate <= 120:
        health_score += 5
    
    project.health_score = round(health_score, 1)
    
    # 设置健康等级
    if project.health_score >= 80:
        project.health_level = '优秀'
        project.health_color = 'green'
    elif project.health_score >= 60:
        project.health_level = '良好'
        project.health_color = 'blue'
    elif project.health_score >= 40:
        project.health_level = '一般'
        project.health_color = 'yellow'
    else:
        project.health_level = '风险'
        project.health_color = 'red'
    
    return project

def build_progress_dashboard_optimized(user, projects):
    """优化构建进度跟踪仪表板数据的函数"""
    today = datetime.now().date()
    
    # 基础统计
    dashboard = {
        'total_projects': len(projects),
        'my_projects': 0,
        'completed_projects': 0,
        'at_risk_projects': 0,
        'overdue_projects': 0,
        'upcoming_milestones': [],
        'recent_reports': [],
        'project_health_distribution': {
            'excellent': 0,
            'good': 0,
            'average': 0,
            'at_risk': 0
        }
    }
    
    # 批量计算项目健康度
    project_ids = [p.id for p in projects]
    
    # 优化查询：一次性获取所有项目的任务统计信息
    task_stats_by_project = db.session.query(
        ProjectTask.project_id,
        func.count(ProjectTask.id).label('total_tasks'),
        func.sum(case([(ProjectTask.status == '已完成', 1)], else_=0)).label('completed_tasks')
    ).filter(ProjectTask.project_id.in_(project_ids)).group_by(ProjectTask.project_id).all()
    
    # 优化查询：一次性获取所有项目的里程碑统计信息
    milestone_stats_by_project = db.session.query(
        ProjectMilestone.project_id,
        func.count(ProjectMilestone.id).label('total_milestones'),
        func.sum(case([(ProjectMilestone.status == '已完成', 1)], else_=0)).label('completed_milestones')
    ).filter(ProjectMilestone.project_id.in_(project_ids)).group_by(ProjectMilestone.project_id).all()
    
    # 优化查询：一次性获取所有项目的成本统计信息
    cost_stats_by_project = db.session.query(
        ProjectCost.project_id,
        func.sum(ProjectCost.amount).label('total_cost')
    ).filter(ProjectCost.project_id.in_(project_ids)).group_by(ProjectCost.project_id).all()
    
    # 转换为字典便于查找
    task_stats_dict = {stat.project_id: stat for stat in task_stats_by_project}
    milestone_stats_dict = {stat.project_id: stat for stat in milestone_stats_by_project}
    cost_stats_dict = {stat.project_id: stat for stat in cost_stats_by_project}
    
    # 计算每个项目的健康度并分类
    for project in projects:
        # 获取项目统计信息
        task_stat = task_stats_dict.get(project.id)
        milestone_stat = milestone_stats_dict.get(project.id)
        cost_stat = cost_stats_dict.get(project.id)
        
        # 计算延期天数
        delay_days = 0
        if project.end_date:
            end_date = project.end_date.date() if isinstance(project.end_date, datetime) else project.end_date
            if end_date < today:
                delay_days = (today - end_date).days
        
        # 计算任务完成率
        task_completion_rate = 0
        if task_stat and task_stat.total_tasks > 0:
            task_completion_rate = (task_stat.completed_tasks or 0) / task_stat.total_tasks * 100
        
        # 计算里程碑完成率
        milestone_completion_rate = 0
        if milestone_stat and milestone_stat.total_milestones > 0:
            milestone_completion_rate = (milestone_stat.completed_milestones or 0) / milestone_stat.total_milestones * 100
        
        # 计算成本使用率
        cost_usage_rate = 0
        if project.budget > 0 and cost_stat:
            cost_usage_rate = (cost_stat.total_cost or 0) / project.budget * 100
        
        # 计算健康度评分
        health_score = 0
        
        # 进度健康度（40%权重）
        if delay_days <= 0:
            health_score += 40
        elif delay_days <= 3:
            health_score += 25
        elif delay_days <= 7:
            health_score += 10
        
        # 任务完成率（30%权重）
        health_score += task_completion_rate * 0.3
        
        # 里程碑完成率（20%权重）
        health_score += milestone_completion_rate * 0.2
        
        # 成本控制（10%权重）
        if cost_usage_rate <= 100:
            health_score += 10
        elif cost_usage_rate <= 120:
            health_score += 5
        
        health_score = round(health_score, 1)
        
        # 设置健康等级
        if health_score >= 80:
            health_level = '优秀'
        elif health_score >= 60:
            health_level = '良好'
        elif health_score >= 40:
            health_level = '一般'
        else:
            health_level = '风险'
        
        # 统计用户负责的项目
        if project.manager_id == user.id:
            dashboard['my_projects'] += 1
        
        # 统计完成和风险项目
        if project.status == '已完成':
            dashboard['completed_projects'] += 1
        elif health_level == '风险':
            dashboard['at_risk_projects'] += 1
        
        # 统计延期项目
        if delay_days > 0:
            dashboard['overdue_projects'] += 1
        
        # 统计健康度分布
        if health_level == '优秀':
            dashboard['project_health_distribution']['excellent'] += 1
        elif health_level == '良好':
            dashboard['project_health_distribution']['good'] += 1
        elif health_level == '一般':
            dashboard['project_health_distribution']['average'] += 1
        elif health_level == '风险':
            dashboard['project_health_distribution']['at_risk'] += 1
    
    # 获取即将到来的里程碑（未来7天内）- 优化查询
    upcoming_milestone_date = today + timedelta(days=7)
    upcoming_milestones = db.session.query(
        ProjectMilestone.id,
        ProjectMilestone.name,
        ProjectMilestone.due_date,
        Project.name.label('project_name'),
        Project.id.label('project_id')
    ).select_from(ProjectMilestone).join(Project, ProjectMilestone.project_id == Project.id).filter(
        and_(
            ProjectMilestone.due_date <= upcoming_milestone_date,
            ProjectMilestone.due_date >= today,
            ProjectMilestone.status != '已完成'
        )
    ).order_by(ProjectMilestone.due_date).limit(10).all()
    
    for milestone in upcoming_milestones:
        dashboard['upcoming_milestones'].append({
            'id': milestone.id,
            'name': milestone.name,
            'project_name': milestone.project_name,
            'project_id': milestone.project_id,
            'due_date': milestone.due_date.strftime('%Y-%m-%d'),
            'days_remaining': (milestone.due_date - today).days
        })
    
    # 获取最近的周报（过去3周内）- 优化查询
    recent_report_date = today - timedelta(days=21)
    recent_reports = db.session.query(
        WeeklyReport.id,
        WeeklyReport.week_start_date,
        WeeklyReport.week_end_date,
        WeeklyReport.weekly_work_hours,
        WeeklyReport.weekly_work_content,
        Project.name.label('project_name'),
        Project.id.label('project_id'),
        User.name.label('user_name')
    ).select_from(WeeklyReport).join(Project, WeeklyReport.project_id == Project.id).join(User, WeeklyReport.user_id == User.id).filter(
        WeeklyReport.week_start_date >= recent_report_date
    ).order_by(WeeklyReport.week_start_date.desc()).limit(10).all()
    
    for report in recent_reports:
        dashboard['recent_reports'].append({
            'id': report.id,
            'project_name': report.project_name,
            'project_id': report.project_id,
            'user_name': report.user_name,
            'week_start_date': report.week_start_date.strftime('%Y-%m-%d'),
            'week_end_date': report.week_end_date.strftime('%Y-%m-%d'),
            'weekly_work_hours': report.weekly_work_hours,
            'weekly_work_content': report.weekly_work_content[:100] + '...' if len(report.weekly_work_content) > 100 else report.weekly_work_content
        })
    
    return dashboard

def get_gantt_data_optimized(project_id):
    """优化获取甘特图数据的函数"""
    # 使用joinedload一次性加载所有关联数据，避免复杂的嵌套加载
    project = Project.query.options(
        joinedload(Project.tasks).joinedload(ProjectTask.assignee),
        joinedload(Project.milestones)
    ).filter_by(id=project_id).first()
    
    if not project:
        return None
    
    # 构建任务数据
    task_data = []
    for task in project.tasks:
        # 计算持续时间，如果开始和结束日期都存在
        duration = 1  # 默认持续时间为1天
        if task.start_date and task.end_date:
            duration = (task.end_date - task.start_date).days + 1  # 加1是因为甘特图包含开始和结束日期
        
        # 确保日期格式正确，使用JavaScript Date对象可以解析的格式
        start_date = task.start_date.strftime('%Y-%m-%d') if task.start_date else None
        
        task_data.append({
            'id': task.id,
            'name': task.name,  # 添加name字段确保与text字段一致
            'text': task.name,
            'start_date': start_date,
            'duration': duration,
            'progress': task.progress / 100 if task.progress else 0,
            'parent': task.parent_id if task.parent_id else 0,
            'assignee': task.assignee.name if task.assignee else '',
            'status': task.status,
            'type': 'task',  # 明确指定任务类型
            'open': True,  # 确保任务默认展开
            'milestone_id': task.milestone_id  # 添加里程碑ID
        })
    
    # 添加里程碑数据到甘特图
    for milestone in project.milestones:
        # 计算里程碑持续时间，里程碑通常是一个时间点，但为了在甘特图中显示，我们设置为1天
        duration = 1
        
        # 确定开始日期，优先使用start_date，否则使用due_date
        start_date = milestone.start_date.strftime('%Y-%m-%d') if milestone.start_date else milestone.due_date.strftime('%Y-%m-%d')
        
        # 为里程碑创建一个特殊的ID，避免与任务ID冲突
        milestone_id = f"milestone_{milestone.id}"
        
        task_data.append({
            'id': milestone_id,
            'name': f"🏁 {milestone.name}",  # 添加里程碑图标前缀
            'text': f"🏁 {milestone.name}",
            'start_date': start_date,
            'duration': duration,
            'progress': milestone.progress / 100 if milestone.progress else 0,
            'parent': 0,  # 里程碑通常是顶级项目
            'assignee': '',
            'status': milestone.status,
            'type': 'milestone',  # 明确指定为里程碑类型
            'open': True,
            'color': '#ff6b6b',  # 里程碑使用特殊颜色
            'milestone_id': milestone.id  # 保存原始里程碑ID
        })
    
    # 构建任务依赖数据 - 优化查询
    links_data = []
    task_ids = [task.id for task in project.tasks]
    dependencies = TaskDependency.query.filter(TaskDependency.task_id.in_(task_ids)).all()
    
    for dep in dependencies:
        # 将数据库中的链接类型映射到DHTMLX甘特图支持的链接类型
        # DHTMLX甘特图支持的链接类型: 0-完成到开始, 1-开始到开始, 2-完成到完成, 3-开始到完成
        link_type = 0  # 默认为"完成到开始"类型
        if dep.dependency_type == 'finish_to_start':
            link_type = 0
        elif dep.dependency_type == 'start_to_start':
            link_type = 1
        elif dep.dependency_type == 'finish_to_finish':
            link_type = 2
        elif dep.dependency_type == 'start_to_finish':
            link_type = 3
            
        links_data.append({
            'id': dep.id,
            'source': dep.depends_on_id,
            'target': dep.task_id,
            'type': link_type
        })
    
    return {
        'data': task_data,
        'links': links_data
    }

def get_work_hour_stats_optimized(project_id=None):
    """优化获取工时统计数据的函数"""
    # 基础统计数据 - 优化查询
    stats = {
        'total_work_hours': db.session.query(func.sum(ProjectTask.work_hour)).scalar() or 0,
        'total_cost': db.session.query(func.sum(ProjectCost.amount)).scalar() or 0,
        'total_projects': Project.query.count(),
        'total_tasks': ProjectTask.query.count()
    }
    
    # 获取用户工时统计 - 优化查询
    user_work_hours = db.session.query(
        User.id,
        User.name,
        func.sum(ProjectTask.work_hour).label('total_hours')
    ).join(ProjectTask, User.id == ProjectTask.assignee_id).group_by(User.id, User.name).having(func.sum(ProjectTask.work_hour) > 0).all()
    
    # 转换为字典格式并按工时排序
    user_work_hours_list = [{'user': {'id': user.id, 'name': user.name}, 'hours': user.total_hours} for user in user_work_hours]
    user_work_hours_list.sort(key=lambda x: x['hours'], reverse=True)
    
    # 如果指定了项目ID，获取项目详情
    selected_project = None
    tasks = []
    costs = []
    
    if project_id:
        selected_project = get_project_details_optimized(project_id)
        if selected_project:
            tasks = selected_project.tasks
            costs = selected_project.costs
    
    return {
        'stats': stats,
        'user_work_hours': user_work_hours_list,
        'selected_project': selected_project,
        'tasks': tasks,
        'costs': costs
    }